---
title: Forecast Monthly Expenditures with Nixtla's StatsForecast and MindsDB
sidebarTitle: Forecast Monthly Expenditures
---

In this tutorial, we'll create a model to forecast expenditures based on historical data using the Nixtla's StatsForecast engine.

## Connect a database

We use a table from our MySQL public demo database, so let’s start by connecting it to MindsDB.

```sql
CREATE DATABASE mysql_historical
WITH ENGINE = 'mysql',
PARAMETERS = {
    "user": "user",
    "password": "MindsDBUser123!",
    "host": "db-demo-data.cwoyhfn6bzs0.us-east-1.rds.amazonaws.com",
    "port": "3306",
    "database": "public"
};
```

Now that we’ve connected our database to MindsDB, let’s query the data to be used in the example:

```sql
SELECT *
FROM mysql_historical.historical_expenditures
LIMIT 3;
```

## Deploy a time-series model

Please note that before using the StatsForecast engine, you should create it with the below command:

```sql
CREATE ML_ENGINE statsforecast
FROM statsforecast;
```

You can check the available engines with this command:

```sql
SHOW ML_ENGINES;
```

Let’s create a model table to forecast the expenditures:

```sql
CREATE MODEL quarterly_expenditure_forecaster
FROM mysql_historical
  (SELECT * FROM historical_expenditures)
PREDICT expenditure
ORDER BY month
GROUP BY category
WINDOW 12
HORIZON 3
USING ENGINE = 'statsforecast';
```

We can check the training status with the following query:

```sql
DESCRIBE quarterly_expenditure_forecaster;
```

## Make predictions

Once the model status is complete, the behavior is the same as with any other AI table – you can query for batch predictions by joining it with a data table.

```sql
SELECT m.month as month, m.expenditure as forecasted
FROM mindsdb.quarterly_expenditure_forecaster as m
JOIN mysql_historical.historical_expenditures as t
WHERE t.month > LATEST
AND t.category = 'food';
```

The `historical_expenditures` table is used to make batch predictions. Upon joining the `quarterly_expenditure_forecaster` model with the `historical_expenditures` table, we get predictions for the next quarter as defined by the `HORIZON 3` clause.

MindsDB provides the `LATEST` keyword that marks the latest training data point. In the `WHERE` clause, we specify the `month > LATEST` condition to ensure the predictions are made for data after the latest training data point.

If we train the model using data from January 2020 until December 2020 (as defined by `WINDOW 12`), then the predictions come for the first quarter of 2021 (as defined by `HORIZON 3`).
